package com.bfsuol.common.util;

import java.io.File;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class CreateXlslHelper{
	public WritableFont font1 = new WritableFont(WritableFont.ARIAL, 12,
			WritableFont.BOLD);
	public WritableFont font2 = new WritableFont(WritableFont.TIMES, 10,
			WritableFont.BOLD);
	public WritableFont font3 = new WritableFont(WritableFont.COURIER, 9,
			WritableFont.BOLD);
	// 大标题格式

	public WritableCellFormat format1 = new WritableCellFormat(font1);
	// 正文字体，居中

	public WritableCellFormat format2 = new WritableCellFormat(font2);
	// 正文字体，居左

	public WritableCellFormat format3 = new WritableCellFormat(font3);

	// bottom字体，居左。

	public CreateXlslHelper() {
		try {
			this.format1.setAlignment(Alignment.CENTRE);
			this.format2.setAlignment(Alignment.CENTRE);
			this.format3.setVerticalAlignment(VerticalAlignment.TOP);
			// this.format1.setBorder(Border.ALL, BorderLineStyle.THIN);
			this.format2.setBorder(Border.ALL, BorderLineStyle.THIN);
			// this.format3.setBorder(Border.ALL, BorderLineStyle.THIN);
			// this.format4.setBorder(Border.ALL, BorderLineStyle.THIN);
			// this.format5.setBorder(Border.ALL, BorderLineStyle.THIN);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public WritableWorkbook createBook(String path, String fileName) {
		WritableWorkbook book = null;
		try {
			File dir = new File(path);
			if (!dir.exists())
				dir.mkdir();
			book = Workbook.createWorkbook(new File(path+"/" + fileName));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return book;
	}
	public SXSSFWorkbook createBook() {
		SXSSFWorkbook book = new SXSSFWorkbook();
		return book;
	}

	public void setSheetColumnWidth(WritableSheet sheet, int[] columnWidths) {
		for (int i = 0; i < columnWidths.length; i++) {
			sheet.setColumnView(i, columnWidths[i]);
		}
	}

	public void setSheetHeader(WritableSheet sheet, int[] columnWidths,
			String[] headerStrings) {
		this.setSheetColumnWidth(sheet, columnWidths);
		// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
		// 定义字体格式
		try {
			// WritableFont font1= new
			// WritableFont(WritableFont.SIMHEI,16,WritableFont.BOLD);
			// WritableCellFormat format1=new WritableCellFormat(font1);
			// 第一行七列合并

			// 参数说明：
			// a 单元格的列号b 单元格的行号c 从单元格[a,b]起，向下合并的列数d 从单元格[a,b]起，向下合并的行数
			// 第一个0：是第0行。

			// 第二个0：第0列

			// 第三个1：合并到第几列。

			// 第四个2：合并到第几行。

			sheet.mergeCells(0, 0, 4, 0);
			// 填写内容
			Label labelTitle1 = new Label(0, 0, headerStrings[0], this.format1);

			// =========================第二行=================================
			// WritableFont font2= new
			// WritableFont(WritableFont.SIMHEI,13,WritableFont.BOLD);
			// WritableCellFormat format2=new WritableCellFormat(font2);
			// 第二行七列合并
			sheet.mergeCells(0, 1, 4, 1);
			// 填写内容
			Label labelTitle2 = new Label(0, 1, headerStrings[1], format3);
			// =========================第三行=================================
			// 第三行，分别合并前三列和后两列。
			sheet.mergeCells(0, 2, 2, 2);
			sheet.mergeCells(3, 2, 4, 2);
			// 填写内容
			Label labelTitle3_1 = new Label(0, 2, headerStrings[2], format3);
			Label labelTitle3_2 = new Label(3, 2, headerStrings[3], format3);

			// =========================第四行=================================
			// 第四行，合并七列。

			sheet.mergeCells(0, 3, 4, 3);
			// 填写内容
			Label labelTitle4 = new Label(0, 3, headerStrings[4], format3);
			// =========================第五行=================================
			// 第五行，合并七列。
			sheet.mergeCells(0, 4, 4, 4);
			// 填写内容
			Label labelTitle5 = new Label(0, 4, headerStrings[5], format3);
			// 将定义好的单元格添加到工作表中
			sheet.addCell(labelTitle1);
			sheet.addCell(labelTitle2);
			sheet.addCell(labelTitle3_1);
			sheet.addCell(labelTitle3_2);
			sheet.addCell(labelTitle4);
			sheet.addCell(labelTitle5);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setSheetDataTableTitle(WritableSheet sheet, int row,
			String[] tableTitles) {
		try {
			// 增加表头
			Label labelTableTitle = null;
			for (int i = 0; i < tableTitles.length; i++) {
				labelTableTitle = new Label(i, row, tableTitles[i], format2);
				sheet.addCell(labelTableTitle);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setSheetData(WritableSheet sheet, int row, String[] datas,
			WritableCellFormat formats) {
		try {
			// 增加数据
			Label dataCell = null;
			for (int i = 0; i < datas.length; i++) {
				dataCell = new Label(i, row, datas[i], formats);
				sheet.addCell(dataCell);
			}
		} catch (Exception e) {
			// System.out.println("in the method setLabelTitle ,the error info is:"+e.getMessage());
			e.printStackTrace();
		}
	}

	public void setSheetData2(WritableSheet sheet, int row, int col,
			String[] datat, int col2, int[] datas, WritableCellFormat formats) {
		try {
			// 增加数据
			Label dataCell = null;
			for (int i = col; i < datat.length; i++) {
				dataCell = new Label(i, row, datat[i], formats);
				sheet.addCell(dataCell);
			}
			jxl.write.Number labelN = null;
			for (int j = 0; j < datas.length; j++) {
				labelN = new jxl.write.Number(col2 + j, row, datas[j]);
				sheet.addCell(labelN);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void setSheetBottom(WritableSheet sheet, int row,
			String[] bottomStrings) {
		try {
			// bottom第一行七列合并
			sheet.mergeCells(0, row, 6, row);
			// 填写内容
			Label bottom1 = new Label(0, row, bottomStrings[0], format1);
			sheet.addCell(bottom1);
		} catch (Exception e) {
			// System.out.println("in the method setSheetBottom ,the error info is:"+e.getMessage());
			e.printStackTrace();
		}
	}

	public void setSheetDataTableTitle(Sheet sheet, int rownumber,
			String[] tableTitles) {
		Row row = sheet.createRow((short) rownumber);
		int cell_index = 0;
		for (int i=0;i<tableTitles.length;i++) {
			String name =tableTitles[i];
			setCell(row.createCell(cell_index),name);
			cell_index++;
		}
	}
	public static void setCell(Cell cell,String name){
		cell.setCellValue(name);
	}

	public void setSheetData(Sheet sheet, int ctr, String[] datas) {
		Row row = sheet.createRow((short) ctr);
		int cell_index = 0;
		for (int i=0;i<datas.length;i++) {
			String name =datas[i];
			setCell(row.createCell(cell_index),name);
			cell_index++;
		}
		
	}

	public void setSheetBottom(Sheet sheet, int number, String[] bottomStrings) {
		Row row = sheet.createRow((short) number);
		setCell(row.createCell(0),bottomStrings[0]);
	}
}